INSERT INTO customer_behaivor_history_month 
    (customer_id, profit, roi, last_update)
WITH GananciasPorVenta AS (
    SELECT 
        c.id AS customer_id,
        s.id AS sale_id,
        DATE_FORMAT(s.sale_date, '%Y-%m-01') as mes,
        -- Corrección: Se considera (qty - returned_qty) para el prorrateo del gasto por fila
        SUM(
            (svi.qty * svi.price) - 
            (svi.qty * (pvi.unit_price + (pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0))))
        ) AS profit_venta,
        -- Corrección: Costo total actualizado con la misma lógica de retornos
        SUM(
            (svi.qty * pvi.unit_price) + (svi.qty * (pvi.cogs_row_amount / NULLIF(pvi.qty - pvi.returned_qty, 0)))
        ) AS costo_venta
    FROM sale s
    INNER JOIN customer c ON s.customer_id = c.id
    INNER JOIN sale_vs_item svi ON s.id = svi.saleId
    INNER JOIN purchase_vs_item pvi ON svi.purchase_vs_itemId = pvi.id
    WHERE s.deleted = 0 
      AND svi.deleted = 0
      AND pvi.deleted = 0
    GROUP BY c.id, s.id, DATE_FORMAT(s.sale_date, '%Y-%m-01')
)
SELECT 
    customer_id,
    SUM(profit_venta) AS profit,
    CASE 
        WHEN SUM(costo_venta) > 0 
        THEN (SUM(profit_venta) / SUM(costo_venta)) * 100 
        ELSE 0 
    END AS roi,
    LAST_DAY(mes) AS last_update
FROM GananciasPorVenta
GROUP BY customer_id, mes;